﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DTO;
using BUS;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
namespace ThuVien
{
    public partial class frmSach : DevComponents.DotNetBar.Office2007RibbonForm 
    {
        SachBUS Sa_BUS = new SachBUS();
        SachDTO sa_DTO = new SachDTO();
       
        public frmSach()
        {
            InitializeComponent();
        }

        
        private void frmSach_Load(object sender, EventArgs e)
        {
            //Sa_BUS.khoitao_KN();
            txtMaSach.Text = Sa_BUS.Phatsinh_MaSach();
            DataSet ds = new DataSet();
            ds = Sa_BUS.Hien_Sach();
            dataGridViewX1.DataSource = ds.Tables[0];
            dataGridViewX1.Rows[0].Selected = Visible;
        }

        private void dataGridViewX1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            sa_DTO.Masach = txtMaSach.Text;
            sa_DTO.Tensach = txtTenSach.Text;
            sa_DTO.Tacgia = txtTacgia.Text;
            sa_DTO.Theloai = txtTheLoai.Text;
            sa_DTO.Tinhtrang = txtTinhTrang.Text;
            sa_DTO.Namxb = NamXB.Value;
            sa_DTO.Manguoinhap = TxtNguoiNhap.Text;
            sa_DTO.Trigia = 15000;
            Sa_BUS.them_Sach(sa_DTO);
            frmSach_Load(sender, e);

            

        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            
            sa_DTO.Masach = txtMaSach.Text;
            sa_DTO.Tensach = txtTenSach.Text;
            sa_DTO.Tacgia = txtTacgia.Text;
            sa_DTO.Theloai = txtTheLoai.Text;
            sa_DTO.Tinhtrang = txtTinhTrang.Text;
            sa_DTO.Namxb = NamXB.Value;
            sa_DTO.Manguoinhap = TxtNguoiNhap.Text;
            sa_DTO.Trigia = 15000;
            Sa_BUS.xoa_sach(sa_DTO);
            frmSach_Load(sender, e);
        }
        int dong;
        private void dataGridViewX1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            //txtMaSach.Text= Sa_BUS.Phatsinh_MaSach();
            try
            {
                dataGridViewX1.Rows[0].Cells[0].Selected = false;
                dong = e.RowIndex;
                txtMaSach.Text = dataGridViewX1.Rows[dong].Cells[0].Value.ToString();
                txtTenSach.Text = dataGridViewX1.Rows[dong].Cells[1].Value.ToString();
                txtTheLoai.Text = dataGridViewX1.Rows[dong].Cells[2].Value.ToString();
                txtTacgia.Text = dataGridViewX1.Rows[dong].Cells[3].Value.ToString();
                NamXB.Value = (DateTime)dataGridViewX1.Rows[dong].Cells[4].Value;
                txtTinhTrang.Text = dataGridViewX1.Rows[dong].Cells[5].Value.ToString();
                TxtNguoiNhap.Text = "NV100";
            }
            catch { 
                txtMaSach.Text = Sa_BUS.Phatsinh_MaSach();
                txtTenSach.Text = "";
                txtTheLoai.Text = "";
                txtTacgia.Text = "";
                //NamXB.Value = null;
                txtTinhTrang.Text = "";
            
            }
                
        }

        private void btnSua_Click(object sender, EventArgs e)
        {

            sa_DTO.Masach = txtMaSach.Text;
            sa_DTO.Tensach = txtTenSach.Text;
            sa_DTO.Tacgia = txtTacgia.Text;
            sa_DTO.Theloai = txtTheLoai.Text;
            sa_DTO.Tinhtrang = txtTinhTrang.Text;
            sa_DTO.Namxb = NamXB.Value;
            sa_DTO.Manguoinhap = TxtNguoiNhap.Text;
            sa_DTO.Trigia = 15000;
            Sa_BUS.Sus_Sach(sa_DTO);
            frmSach_Load(sender, e);
     

        }

        private void btnMoi_Click(object sender, EventArgs e)
        {
            txtMaSach.Text = Sa_BUS.Phatsinh_MaSach();
            txtTenSach.Text = "";
            txtTheLoai.Text = "";
            txtTacgia.Text = "";
            txtTinhTrang.Text = "";
            TxtNguoiNhap.Text = "NV100";
            NamXB.Value = DateTime.Today;
            NgayNhap.Value = DateTime.Today;
            //Focus(txtMaSach.Text );
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn muốn thoát khỏi màn hình??", "THOÁT!!", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
            {
                this.Close();
                
            }
        }

        private void btnTimKiem_Click(object sender, EventArgs e)
        {
            frmTraCuuSach f = new frmTraCuuSach();
            f.Show();
            this.Close();
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "*.xls|*.xlsx";
            openFile.ShowDialog();
            string path="";
            path=openFile.FileName.ToString();            
            Sa_BUS.khoitao_KN();
            DataSet ds = new DataSet();
            ds = Sa_BUS.Hien_Sach();
            dataGridViewX1.DataSource = ds.Tables[0];
            //dataGridView2.DataSource = DASET.Tables[0];
            OleDbConnection connect2 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+path +"; " + "Extended Properties=Excel 8.0");
            connect2.Open();
            string masach = "";
            string tensach = "";
            string theloai = "";
            string tacgia="";
            DateTime namxb=DateTime.Today;
            string nhaxb = "";
            string tinhtrang = "";
            string manguoinhap = "NV100";
            decimal trigia = 1000;
            if (connect2.State == ConnectionState.Open)
            {
                int dem = 0;
                for (int i = 0; i < dataGridViewX1 .Rows.Count - 1; i++)
                {
                     masach= dataGridViewX1 .Rows[i].Cells[0].Value.ToString();
                    tensach= dataGridViewX1 .Rows[i].Cells[1].Value.ToString();
                    theloai = dataGridViewX1 .Rows[i].Cells[2].Value.ToString();
                    tacgia = dataGridViewX1 .Rows[i].Cells[3].Value.ToString();
                    namxb  = (DateTime )dataGridViewX1 .Rows[i].Cells[4].Value;
                    nhaxb  = dataGridViewX1 .Rows[i].Cells[5].Value.ToString();
                    tinhtrang = dataGridViewX1 .Rows[i].Cells[6].Value.ToString();
                    // MessageBox.Show("ket noi file Excelthanh cong");
                    string sql = "insert into [Sheet1$] values('" + masach + "','" + tensach  + "','" + theloai  + "',";
                    sql += "'"+tacgia +"','"+nhaxb +"','"+ namxb +"','"+tinhtrang +"','"+manguoinhap +"',"+trigia +") ";
                    OleDbCommand command = new OleDbCommand(sql, connect2);
                    try
                    {
                        command.ExecuteNonQuery();
                        command.Dispose();
                        dem++;
                    }
                    catch { };

                }
                MessageBox.Show("da the dc '" + dem + " dong");

            }
        }

        private void btnImPort_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "*.xls|*.xlsx";
            openFile.ShowDialog();
            string path = "";
            path = openFile.FileName.ToString();
            SqlConnection con = Sa_BUS.ChuoiKN_Load();
            con.Open();
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            //DataGridView Data = new DataGridView();
            //DataSet ds = new DataSet();
            //ds = Sa_BUS.Hien_Sach();
            //Data.DataSource = ds.Tables[0];
           //dataGridViewX1.DataSource = ds.Tables[0];
            //dataGridView2.DataSource = DASET.Tables[0];
            
                

                    OleDbConnection connect = new OleDbConnection();
                    connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+path +"; " + "Extended Properties=Excel 8.0";
                    connect .Open();
                    if (connect.State == ConnectionState.Open)
                    {
                        MessageBox.Show("thanh cong");
                        OleDbCommand cmd = new OleDbCommand("select MaSach, TenSach,TheLoai,TacGia,NamXuatBan,TinhTrang,NhaXuatBan from [Sheet2$]",connect );
                        cmd.ExecuteNonQuery();
                        DataSet ds = new DataSet();
                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                        con.Close();
                    }
                    else
                    {
                        MessageBox.Show("ket noi bi loi", "tthong bao", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
                    }
                    // string STT = "";
                    string MaSach = "";
                    string TenSach = "";
                    string TheLoai="";
                    string TacGia="";
                    DateTime NamXB=DateTime.Today;
                    string NhaXb="";
                    string TinhTrang="";
                    Decimal TriGia=14000;
                    String MaNguoiNhap="NV100";
                    int stt = 0;
                    //SqlDataAdapter sql_da = new SqlDataAdapter();
                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {

                        //STT = dataGridView1.Rows[i].Cells[0].Value.ToString();
                        MaSach = dataGridView1.Rows[i].Cells[0].Value.ToString();
                        TenSach = dataGridView1.Rows[i].Cells[1].Value.ToString();
                        TheLoai = dataGridView1.Rows[i].Cells[2].Value.ToString();
                        TacGia= dataGridView1.Rows[i].Cells[3].Value.ToString();
                        NhaXb= dataGridView1.Rows[i].Cells[6].Value.ToString();
                        TinhTrang= dataGridView1.Rows[i].Cells[5].Value.ToString();
                       
                        try
                        {
                            if (con.State == ConnectionState.Closed)
                            {
                                con.Open();
                            }
                            string sql = "insert into sach(masach,tensach,theloai,tacgia,namxuatban,nhaxuatban,tinhtrang,trigia,manguoinhap) values('"+MaSach +"',";
                            sql+="'"+TenSach +"',";
                            sql+="'"+TheLoai +"','"+TacGia +"','"+NamXB +"',";
                           
                            sql+="'"+NhaXb  +"',";
                            sql+="'"+TinhTrang  +"',";
                            sql+=""+TriGia +",'"+MaNguoiNhap +"')";
                            SqlCommand command = new SqlCommand(sql, con);
                            command.ExecuteNonQuery();
                            command.Dispose();
                            stt++;

                        }
                        catch
                        {
                            MessageBox.Show("Da them duoc " + stt + " dong");
                            //MessageBox.Show("bi loi o dong "+ stt +1 + " , hoten='"+temp1 +"' va ngay sinh='"+temp2 +"'");


                        };
                    }
                    MessageBox.Show("Da Them dc " + stt + "dong , Thong Bao");
                    frmSach_Load(sender, e);
            con.Close();
                }
                

            

    }
        

      
        
        
        
    }

